﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Data.Sql;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.IO;
using Crownwood.Magic.Controls;
using ConnectSQLServer.Common;

namespace ConnectSQLServer
{
    public partial class AddConnection : Form
    {
        private ServerConnection m_ServerConnection;
        private Server m_Server;

        public AddConnection()
        {
            InitializeComponent();
        }

        private void btRefresh_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
            cbServerName.DisplayMember = "Name";
            cbServerName.DataSource = dt;
            cbDatabaseName.DataSource = null;
            this.Cursor = Cursors.Default;
        }

        private void AddConnection_Load(object sender, EventArgs e)
        {
            radWinAuthentication.Checked = true;
            lbName.Enabled = false;
            tbUsername.Enabled = false;
            lbPassword.Enabled = false;
            tbPassword.Enabled = false;
            
            radDatabaseName.Checked = true;
            tbDatabaseFile.Enabled = false;
            btBrowse.Enabled = false;
            tbLogicalFilename.Enabled = false;            
            
        }

        private void radServerAuthentication_CheckedChanged(object sender, EventArgs e)
        {
            if (radServerAuthentication.Checked == true)
            {
                lbName.Enabled = true;
                tbUsername.Enabled = true;
                lbPassword.Enabled = true;
                tbPassword.Enabled = true;
            }
            else
            {
                lbName.Enabled = false;
                tbUsername.Enabled = false;
                tbUsername.Text = String.Empty;
                lbPassword.Enabled = false;
                tbPassword.Text = String.Empty;
                tbPassword.Enabled = false;
            }
            cbDatabaseName.Items.Clear();
        }

        private void radAttachDatabaseFile_CheckedChanged(object sender, EventArgs e)
        {
            if (radAttachDatabaseFile.Checked == true)
            {
                tbDatabaseFile.Enabled = true;
                btBrowse.Enabled = true;
                tbLogicalFilename.Enabled = true;
                cbDatabaseName.Enabled = false;
            }
            else
            {
                cbDatabaseName.Enabled = true;
                tbDatabaseFile.Enabled = false;
                tbDatabaseFile.Text = String.Empty;
                btBrowse.Enabled = false;
                tbLogicalFilename.Enabled = false;
                tbLogicalFilename.Text = String.Empty;
            }
        }

        private void cbServerName_DropDown(object sender, EventArgs e)
        {
            try
            {
                if (cbServerName.DataSource == null)
                {
                    this.Cursor = Cursors.WaitCursor;
                    DataTable dt = SmoApplication.EnumAvailableSqlServers(false);
                    cbServerName.DisplayMember = "Name";
                    cbServerName.DataSource = dt;
                    this.Cursor = Cursors.Default;
                }
            }
            catch
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void cbDatabaseName_DropDown(object sender, EventArgs e)
        {
            try
            {
                if (cbDatabaseName.Items.Count == 0)
                {
                    if (radWinAuthentication.Checked == true)
                    {
                        this.Cursor = Cursors.WaitCursor;
                        m_ServerConnection = new ServerConnection(cbServerName.Text);
                        m_Server = new Server(m_ServerConnection);
                        foreach (object a in m_Server.Databases)
                            cbDatabaseName.Items.Add(a.ToString().Substring(1,a.ToString().Length-2));
                        this.Cursor = Cursors.Default;
                    }
                    else
                    {
                        this.Cursor = Cursors.WaitCursor;
                        m_ServerConnection = new ServerConnection(cbServerName.Text,tbUsername.Text,tbPassword.Text);
                        m_Server = new Server(m_ServerConnection);
                        foreach (object a in m_Server.Databases)
                            cbDatabaseName.Items.Add(a.ToString().Substring(1, a.ToString().Length - 2));
                        this.Cursor = Cursors.Default;
                    }
                }
                
                    
            }
            catch 
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void cbServerName_SelectedIndexChanged(object sender, EventArgs e)
        {
            cbDatabaseName.Items.Clear();
        }

        private void btBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Attach the database file " ;
            ofd.InitialDirectory = @"C:\";
            ofd.Filter = "mdf files (*.mdf)|*.mdf";
            ofd.RestoreDirectory = true;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                tbDatabaseFile.Text = ofd.FileName;
            }
        }

        private void btCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btTestConnection_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {
                // If a server is selected
                if (!string.IsNullOrEmpty(cbServerName.Text))
                {
                    string message = this.ConnectDatabase();
                    if (string.IsNullOrEmpty(message))
                    {
                        MessageBox.Show("Test connection succeeded", "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        MessageBox.Show("This connection can not tested because " + message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        this.Cursor = Cursors.Default;
                        return;
                    }
                }
                else
                {
                    // A server was not selected, show an error message
                    this.Cursor = Cursors.Default;
                    MessageBox.Show("Please select a server first", "Server not selected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
            }
            catch (Exception ex)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show(ex.Message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            this.Cursor = Cursors.Default;
        }

        private string ConnectDatabase()
        {
            if (!string.IsNullOrEmpty(cbServerName.Text))
            {
                try
                {
                    this.m_ServerConnection = new ServerConnection(cbServerName.Text.ToString());
                    //this.m_ServerConnection
                    //First Check type of Authentication
                    if (this.radWinAuthentication.Checked == true)   //Windows Authentication
                    {
                        this.m_ServerConnection.LoginSecure = true;
                        if (this.radDatabaseName.Checked == true)
                        {
                            this.m_ServerConnection.DatabaseName = this.cbDatabaseName.Text;  //Database Name
                            this.m_Server = new Server(this.m_ServerConnection);
                            if (this.m_Server.Databases[cbDatabaseName.Text] == null && !string.IsNullOrEmpty(cbDatabaseName.Text))
                                return "The specified database does not exist or is not visible to the specified user.";
                        }
                        else
                        { 
                            this.m_Server = new Server(this.m_ServerConnection);
                            string sDBName;
                            if (!string.IsNullOrEmpty(tbLogicalFilename.Text))
                                sDBName = tbLogicalFilename.Text;
                            else
                                sDBName = tbDatabaseFile.Text.ToUpper();
                            string message = AttachDatabase(m_Server, sDBName);
                            return message;
                        }
                        
                    }
                    else
                    {
                        // Create a new connection to the selected server name
                        this.m_ServerConnection.LoginSecure = false;
                        this.m_ServerConnection.Login = this.tbUsername.Text;       //Login User
                        this.m_ServerConnection.Password = this.tbPassword.Text;    //Login Password
                        if (this.radDatabaseName.Checked == true)
                        {
                            this.m_ServerConnection.DatabaseName = this.cbDatabaseName.Text;  //Database Name
                            this.m_Server = new Server(this.m_ServerConnection);
                            if (this.m_Server.Databases[cbDatabaseName.Text] == null && !string.IsNullOrEmpty(cbDatabaseName.Text))
                                return "The specified database does not exist or is not visible to the specified user.";
                        }
                        else
                        {
                            this.m_Server = new Server(this.m_ServerConnection);
                            string sDBName;
                            if (!string.IsNullOrEmpty(tbLogicalFilename.Text))
                                sDBName = tbLogicalFilename.Text;
                            else
                                sDBName = tbDatabaseFile.Text.ToUpper();
                            string message = AttachDatabase(m_Server, sDBName);
                            return message;
                        }
                    }
                    return string.Empty;
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            return "No server selected";
        }

        private string AttachDatabase(Server s,string sDBName)
        {
            StringCollection cFiles = new StringCollection();
            try
            {
                if (File.Exists(tbDatabaseFile.Text))
                {
                    cFiles.Add(tbDatabaseFile.Text);
                    s.AttachDatabase(sDBName, cFiles,AttachOptions.RebuildLog);
                }
                else
                {
                    throw new Exception("MDF File does not exist. " + tbDatabaseFile.Text);
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        private void btOK_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {
                // If a server is selected
                if (!string.IsNullOrEmpty(cbServerName.Text))
                {
                    string message = this.ConnectDatabase();
                    if (string.IsNullOrEmpty(message))
                    {
                        GlobalVariable.DBExplorerManager.AddConnection(m_Server, m_ServerConnection);
                        this.Close();
                    }
                    else
                    {
                        MessageBox.Show("This connection can not created because " + message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        this.Cursor = Cursors.Default;
                        return;
                    }
                }
                else
                {
                    // A server was not selected, show an error message
                    this.Cursor = Cursors.Default;
                    MessageBox.Show("Please select a server first", "Server not selected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    return;
                }
            }
            catch (Exception ex)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show(ex.Message, "SQL Authetication", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            this.Cursor = Cursors.Default;
        }





    }
}
